CREATE VIEW [dbo].[vSoaGroupMemberDetailRelationship]
WITH SCHEMABINDING
AS
    SELECT  CAST('RELATIONSHIP-' + [r].[ID] + ':' + [r].[TARGET_ID] + ':'
            + CAST([r].[SEQN] AS VARCHAR(10)) AS VARCHAR(50)) AS [GroupMemberDetailId],
            CAST(SUBSTRING('RELATIONSHIP-' + [r].[ID] + ':'
                           + CASE WHEN [r].[TARGET_ID] > ''
                                  THEN [r].[TARGET_ID]
                                  ELSE [r].[TARGET_NAME]
                             END, 1, 50) AS VARCHAR(50)) AS [GroupMemberId],
            [r].[TARGET_ID] AS [PartyId],
            [r].[EFFECTIVE_DATE] AS [EffectiveDate],
            [r].[THRU_DATE] AS [ExpirationDate],
            CAST('RELATIONSHIP:' + [rt].[RELATION_TYPE] AS VARCHAR(30)) AS [GroupRoleId],
            CAST([rt].[RELATION_TYPE] AS VARCHAR(100)) AS [RoleName],
            CAST([rt].[DESCRIPTION] AS VARCHAR(100)) AS [RoleDescription],
            CAST(NULL AS INT) AS [RolePriority],
            CAST([r].[TITLE] AS VARCHAR(100)) AS [Title],
            [r].[SEQN],
            [r].[STATUS]
      FROM  [dbo].[Relationship] r
            INNER JOIN [dbo].[Relationship_Types] [rt] ON [r].[RELATION_TYPE] = [rt].[RELATION_TYPE]



GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaGroupMemberDetailRelationship] ON [dbo].[vSoaGroupMemberDetailRelationship] ([GroupMemberDetailId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberDetailRelationship_GroupMemberId] ON [dbo].[vSoaGroupMemberDetailRelationship] ([GroupMemberId]) ON [PRIMARY]
GO